﻿CREATE PROCEDURE reports.s_CollectorActivity
AS

DECLARE @NoteDateStart datetime SET @NoteDateStart = dbo.Today(DATEADD(yy,-2,GETDATE()))
--SELECT @NoteDateStart = MAX(NoteDate0) FROM reports.t_CollectionActivity
PRINT 'Generating collector activity after '+convert(varchar,@NoteDateStart,101)

--DELETE FROM reports.t_CollectionActivity WHERE NoteDate0 > @NoteDateStart

--INSERT INTO reports.t_CollectionPTP
SELECT     Notes.NoteUser, Notes.NoteDate0, Notes.Count, Notes.PTPs, Notes.PPTPs, Notes.BPTPs
INTO reports.t_CollectionPTP
FROM         (SELECT     NoteUser, NoteDate0, COUNT(*) AS Count, SUM(PTP) AS PTPs, SUM(PPTP) AS PPTPs, SUM(BPTP) AS BPTPs
FROM          (SELECT     N_1.LoanID, N_1.NoteUser, N_1.NoteDate0, SUM(CASE SL.StateTo WHEN dbo.fs_StateID__Notes__PromisedToPay() 
THEN 1 ELSE 0 END) AS PTP, SUM(CASE N_1.statusid WHEN dbo.fs_StateID__Notes__PaymentRecieved() THEN 1 ELSE 0 END) 
AS PPTP, SUM(CASE N_1.statusid WHEN dbo.fs_StateID__Notes__BrokenPromise() THEN 1 ELSE 0 END) AS BPTP
FROM          (SELECT     N.ID, N.LoanID, N.Note, N.StatusID, N.StatusDate, N.StatusUser, N.DateDue, N.DateDueBy, N.NoteTypeID, 
N.NoteActionCodeID, N.Phone, N.PhoneCategoryID, N.NoteDate, N.IsTask, N.NoteUser, N.NoteDate0, N.NoteDate1, 
N.IsResolved
FROM          t_Note AS N WITH(nolock)INNER JOIN
v_ADS_CollectorsBySAM AS C ON N.NoteUser = C.sAMAccountName WHERE      (N.NoteDate0 > @NoteDateStart)) AS N_1 INNER JOIN
tlog_State AS SL ON N_1.ID = SL.TablePK AND N_1.NoteDate0 = SL.Date
GROUP BY N_1.LoanID, N_1.NoteUser, N_1.NoteDate0) AS T
GROUP BY NoteUser, NoteDate0) AS Notes
